create database VehiclePolicy
go

use VehiclePolicy

drop table SEC_USER
create table SEC_USER(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	account varchar(50) unique,
	"password"  varchar(32),
	name  varchar(50),
	phone  varchar(50),
	email  varchar(100),
	office_ID bigint
)
go

drop table USER_ROLE
create table USER_ROLE(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	"user_ID" bigint,
	"role_ID" bigint
)
go

drop table SEC_ROLE
create table SEC_ROLE(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max)
)
go

drop table CUSTOMER
create table CUSTOMER(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	pin varchar(20),
	name varchar(50),
	gender varchar(20),
	birthday datetime,
	phone varchar(50),
	permanent_Addr varchar(100),
	current_Addr varchar(100),
	rate_Level int,
	indemnity_Level int,
	office_ID bigint
)
go

drop table CARTYPE
create table CARTYPE(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max),
	purpose varchar(50),
	"type" varchar(50),
	cc_Min int,
	cc_Max int
)
go

drop table BRAND
create table BRAND(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max)
)
go

drop table CARMODEL
create table CARMODEL(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(max),
	eng_Name varchar(50),
	descript varchar(max),
	carseries_ID bigint,
	cc int
)
go

drop table PRODUCT
create table PRODUCT(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max)
)
go

drop table PRODUCT_INSITEM
create table PRODUCT_INSITEM(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	product_ID bigint,
	insitem_ID bigint
)
go

drop table INSITEM
create table INSITEM(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max),
	"type" varchar(20),
	corresponding_Table varchar(50)
)
go

drop table POLICY
create table POLICY(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(10) ,
	version_No bigint,
	code varchar(20) unique,
	underwriter_User_ID bigint,
	applicant_Customer_ID bigint,
	insurant_Customer_ID bigint,
	car_No varchar(20),
	carmodel_ID bigint,
	production_Date datetime,
	tonnage real,
	seats int,
	product_ID bigint,
	"start_Date" datetime,
	"end_Date" datetime,
	total_Rate bigint,
	assessor_User_ID bigint,
	assessor_Date datetime,
	office_ID bigint,
	purpose varchar(50)
)
go

drop table POLICY_ITEM
create table POLICY_ITEM(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	policy_ID bigint,
	insitem_ID bigint,
	person_Money bigint,
	case_Money bigint,
	rate bigint
)
go

drop table OFFICE
create table OFFICE(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50) unique,
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max)
)
go

drop table RATE_21
create table RATE_21(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	cartype_ID bigint,
	gender varchar(20),
	from_Age int,
	to_Age int,
	tonnage_Min real,
	tonnage_Max real,
	seats_Min int,
	seats_Max int,
	rate varchar(max)
)
go

drop table RATE_31
create table RATE_31(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	cartype_ID bigint,
	person_Money int,
	case_Money int,
	rate bigint
)
go

drop table RATE_32
create table RATE_32(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	cartype_ID bigint,
	case_Money int,
	rate bigint
)
go

drop table HUMAN_COEF
create table HUMAN_COEF(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	gender varchar(20),
	from_Age int,
	to_Age int,
	coef real
)
go

drop table INDEMNITY_COEF
create table INDEMNITY_COEF(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	"level" int unique,
	coef real 
)
go

drop table POLICY_REQ
create table POLICY_REQ(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	"type" varchar(20) unique,
	last_Policy_No bigint
)
go

drop table BLACKLIST
create table BLACKLIST(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	number varchar(50) unique,
	"type" varchar(50),
	reason varchar(max)
)
go

drop table CARSERIES
create table CARSERIES(
	ID bigint identity primary key ,
	created_By bigint,
	created_Date datetime,
	last_Modified_By bigint,
	last_Modified_Date datetime,
	"status" varchar(20) ,
	version_No bigint,
	code varchar(50),
	local_Name varchar(50),
	eng_Name varchar(50),
	descript varchar(max),
	brand_ID bigint,
	"type" varchar(50)
)
go